package com.example.simplesvc;

import jxl.*;
import jxl.format.*;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.*;
import jxl.write.Boolean;
import jxl.write.Number;
import org.junit.jupiter.api.Test;



import java.io.File;
import java.net.MalformedURLException;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;

public class JXLCase {


    private String filename;
    private WritableWorkbook workbook;

    @Test
    public void testXlsxRead() {

    }

    @Test
    public void testXlsRW() throws Exception{

        filename = "d:\\tmp\\jxl.xlsx";
        WorkbookSettings ws = new WorkbookSettings();
        ws.setLocale(new Locale("en", "EN"));
        this.workbook = Workbook.createWorkbook(new File(this.filename), ws);
        WritableSheet s2 = this.workbook.createSheet("Number Formats", 0);
        WritableSheet s3 = this.workbook.createSheet("Date Formats", 1);
        WritableSheet s1 = this.workbook.createSheet("Label Formats", 2);
        WritableSheet s4 = this.workbook.createSheet("Borders", 3);
        WritableSheet s5 = this.workbook.createSheet("Labels", 4);
        WritableSheet s6 = this.workbook.createSheet("Formulas", 5);
        //WritableSheet s7 = this.workbook.createSheet("Images", 6);
        this.writeLabelFormatSheet(s1);
        this.writeNumberFormatSheet(s2);
        this.writeDateFormatSheet(s3);
        this.writeBordersSheet(s4);
        this.writeLabelsSheet(s5);
        this.writeFormulaSheet(s6);
//        this.writeImageSheet(s7);
        this.workbook.setColourRGB(Colour.LIME, 255, 0, 0);
        this.workbook.write();
        this.workbook.close();
    }

    private void writeNumberFormatSheet(WritableSheet s) throws WriteException {
        WritableCellFormat wrappedText = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT);
        wrappedText.setWrap(true);
        s.setColumnView(0, 20);
        s.setColumnView(4, 20);
        s.setColumnView(5, 20);
        s.setColumnView(6, 20);
        Label l = new Label(0, 0, "+/- Pi - default format", wrappedText);
        s.addCell(l);
        jxl.write.Number n = new jxl.write.Number(1, 0, 3.1415926535);
        s.addCell(n);
        n = new jxl.write.Number(2, 0, -3.1415926535);
        s.addCell(n);
        l = new Label(0, 1, "+/- Pi - integer format", wrappedText);
        s.addCell(l);
        WritableCellFormat cf1 = new WritableCellFormat(NumberFormats.INTEGER);
        n = new jxl.write.Number(1, 1, 3.1415926535, cf1);
        s.addCell(n);
        n = new jxl.write.Number(2, 1, -3.1415926535, cf1);
        s.addCell(n);
        l = new Label(0, 2, "+/- Pi - float 2dps", wrappedText);
        s.addCell(l);
        WritableCellFormat cf2 = new WritableCellFormat(NumberFormats.FLOAT);
        n = new jxl.write.Number(1, 2, 3.1415926535, cf2);
        s.addCell(n);
        n = new jxl.write.Number(2, 2, -3.1415926535, cf2);
        s.addCell(n);
        l = new Label(0, 3, "+/- Pi - custom 3dps", wrappedText);
        s.addCell(l);
        NumberFormat dp3 = new NumberFormat("#.###");
        WritableCellFormat dp3cell = new WritableCellFormat(dp3);
        n = new jxl.write.Number(1, 3, 3.1415926535, dp3cell);
        s.addCell(n);
        n = new jxl.write.Number(2, 3, -3.1415926535, dp3cell);
        s.addCell(n);
        l = new Label(0, 4, "+/- Pi - custom &3.14", wrappedText);
        s.addCell(l);
        NumberFormat pounddp2 = new NumberFormat("&#.00");
        WritableCellFormat pounddp2cell = new WritableCellFormat(pounddp2);
        n = new jxl.write.Number(1, 4, 3.1415926535, pounddp2cell);
        s.addCell(n);
        n = new jxl.write.Number(2, 4, -3.1415926535, pounddp2cell);
        s.addCell(n);
        l = new Label(0, 5, "+/- Pi - custom Text #.### Text", wrappedText);
        s.addCell(l);
        NumberFormat textdp4 = new NumberFormat("Text#.####Text");
        WritableCellFormat textdp4cell = new WritableCellFormat(textdp4);
        n = new jxl.write.Number(1, 5, 3.1415926535, textdp4cell);
        s.addCell(n);
        n = new jxl.write.Number(2, 5, -3.1415926535, textdp4cell);
        s.addCell(n);
        l = new Label(4, 0, "+/- Bilko default format");
        s.addCell(l);
        n = new jxl.write.Number(5, 0, 1.5042699E7);
        s.addCell(n);
        n = new jxl.write.Number(6, 0, -1.5042699E7);
        s.addCell(n);
        l = new Label(4, 1, "+/- Bilko float format");
        s.addCell(l);
        WritableCellFormat cfi1 = new WritableCellFormat(NumberFormats.FLOAT);
        n = new jxl.write.Number(5, 1, 1.5042699E7, cfi1);
        s.addCell(n);
        n = new jxl.write.Number(6, 1, -1.5042699E7, cfi1);
        s.addCell(n);
        l = new Label(4, 2, "+/- Thousands separator");
        s.addCell(l);
        WritableCellFormat cfi2 = new WritableCellFormat(NumberFormats.THOUSANDS_INTEGER);
        n = new jxl.write.Number(5, 2, 1.5042699E7, cfi2);
        s.addCell(n);
        n = new jxl.write.Number(6, 2, -1.5042699E7, cfi2);
        s.addCell(n);
        l = new Label(4, 3, "+/- Accounting red - added 0.01");
        s.addCell(l);
        WritableCellFormat cfi3 = new WritableCellFormat(NumberFormats.ACCOUNTING_RED_FLOAT);
        n = new jxl.write.Number(5, 3, 1.504269901E7, cfi3);
        s.addCell(n);
        n = new jxl.write.Number(6, 3, -1.504269901E7, cfi3);
        s.addCell(n);
        l = new Label(4, 4, "+/- Percent");
        s.addCell(l);
        WritableCellFormat cfi4 = new WritableCellFormat(NumberFormats.PERCENT_INTEGER);
        n = new jxl.write.Number(5, 4, 1.5042699E7, cfi4);
        s.addCell(n);
        n = new jxl.write.Number(6, 4, -1.5042699E7, cfi4);
        s.addCell(n);
        l = new Label(4, 5, "+/- Exponential - 2dps");
        s.addCell(l);
        WritableCellFormat cfi5 = new WritableCellFormat(NumberFormats.EXPONENTIAL);
        n = new jxl.write.Number(5, 5, 1.5042699E7, cfi5);
        s.addCell(n);
        n = new jxl.write.Number(6, 5, -1.5042699E7, cfi5);
        s.addCell(n);
        l = new Label(4, 6, "+/- Custom exponentional - 3dps", wrappedText);
        s.addCell(l);
        NumberFormat edp3 = new NumberFormat("0.000E0");
        WritableCellFormat edp3Cell = new WritableCellFormat(edp3);
        n = new jxl.write.Number(5, 6, 1.5042699E7, edp3Cell);
        s.addCell(n);
        n = new jxl.write.Number(6, 6, -1.5042699E7, edp3Cell);
        s.addCell(n);
        l = new Label(4, 7, "Custom neg brackets", wrappedText);
        s.addCell(l);
        NumberFormat negbracks = new NumberFormat("#,##0;(#,##0)");
        WritableCellFormat negbrackscell = new WritableCellFormat(negbracks);
        n = new jxl.write.Number(5, 7, 1.5042699E7, negbrackscell);
        s.addCell(n);
        n = new jxl.write.Number(6, 7, -1.5042699E7, negbrackscell);
        s.addCell(n);
        l = new Label(4, 8, "Custom neg brackets 2", wrappedText);
        s.addCell(l);
        NumberFormat negbracks2 = new NumberFormat("#,##0;(#,##0)a");
        WritableCellFormat negbrackscell2 = new WritableCellFormat(negbracks2);
        n = new jxl.write.Number(5, 8, 1.5042699E7, negbrackscell2);
        s.addCell(n);
        n = new jxl.write.Number(6, 8, -1.5042699E7, negbrackscell2);
        s.addCell(n);
        l = new Label(4, 9, "Custom percent", wrappedText);
        s.addCell(l);
        NumberFormat cuspercent = new NumberFormat("0.0%");
        WritableCellFormat cuspercentf = new WritableCellFormat(cuspercent);
        n = new jxl.write.Number(5, 9, 3.14159265, cuspercentf);
        s.addCell(n);
        l = new Label(0, 10, "Boolean - TRUE");
        s.addCell(l);
        jxl.write.Boolean b = new jxl.write.Boolean(1, 10, true);
        s.addCell(b);
        l = new Label(0, 11, "Boolean - FALSE");
        s.addCell(l);
        b = new Boolean(1, 11, false);
        s.addCell(b);
        l = new Label(0, 12, "A hidden cell->");
        s.addCell(l);
        n = new jxl.write.Number(1, 12, 17.0, WritableWorkbook.HIDDEN_STYLE);
        s.addCell(n);

        int row;
        int col;
        for(row = 0; row < 100; ++row) {
            for(col = 8; col < 108; ++col) {
                n = new jxl.write.Number(col, row, (double)(col + row));
                s.addCell(n);
            }
        }

        for(row = 101; row < 3000; ++row) {
            for(col = 0; col < 25; ++col) {
                n = new jxl.write.Number(col, row, (double)(col + row));
                s.addCell(n);
            }
        }

    }

    private void writeDateFormatSheet(WritableSheet s) throws WriteException {
        WritableCellFormat wrappedText = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT);
        wrappedText.setWrap(true);
        s.setColumnView(0, 20);
        s.setColumnView(2, 20);
        s.setColumnView(3, 20);
        s.getSettings().setFitWidth(2);
        s.getSettings().setFitHeight(2);
        Calendar c = Calendar.getInstance();
        c.set(1975, 4, 31, 15, 21, 45);
        c.set(14, 660);
        Date date = c.getTime();
        c.set(1900, 0, 1, 0, 0, 0);
        c.set(14, 0);
        Date date2 = c.getTime();
        Date date3 = new Date(0L);
        c.set(1918, 10, 11, 10, 0, 0);
        Date date4 = c.getTime();
        c.set(1900, 0, 2, 0, 0, 0);
        Date date5 = c.getTime();
        c.set(1901, 0, 1, 0, 0, 0);
        Date date6 = c.getTime();
        c.set(1900, 4, 31, 0, 0, 0);
        Date date7 = c.getTime();
        c.set(1900, 1, 1, 0, 0, 0);
        Date date8 = c.getTime();
        c.set(1900, 0, 31, 0, 0, 0);
        Date date9 = c.getTime();
        c.set(1900, 2, 1, 0, 0, 0);
        Date date10 = c.getTime();
        c.set(1900, 1, 27, 0, 0, 0);
        Date date11 = c.getTime();
        c.set(1900, 1, 28, 0, 0, 0);
        Date date12 = c.getTime();
        SimpleDateFormat sdf = new SimpleDateFormat("dd MMM yyyy HH:mm:ss.SSS");
        Label l = new Label(0, 0, "All dates are " + sdf.format(date), wrappedText);
        s.addCell(l);
        l = new Label(0, 1, "Built in formats", wrappedText);
        s.addCell(l);
        l = new Label(2, 1, "Custom formats");
        s.addCell(l);
        WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1);
        DateTime dt = new DateTime(0, 2, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT2);
        dt = new DateTime(0, 3, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT3);
        dt = new DateTime(0, 4, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT4);
        dt = new DateTime(0, 5, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT5);
        dt = new DateTime(0, 6, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT6);
        dt = new DateTime(0, 7, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT7);
        dt = new DateTime(0, 8, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT8);
        dt = new DateTime(0, 9, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(0, 10, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT10);
        dt = new DateTime(0, 11, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT11);
        dt = new DateTime(0, 12, date, cf1);
        s.addCell(dt);
        cf1 = new WritableCellFormat(DateFormats.FORMAT12);
        dt = new DateTime(0, 13, date, cf1);
        s.addCell(dt);
        DateFormat df = new DateFormat("dd MM yyyy");
        cf1 = new WritableCellFormat(df);
        l = new Label(2, 2, "dd MM yyyy");
        s.addCell(l);
        dt = new DateTime(3, 2, date, cf1);
        s.addCell(dt);
        df = new DateFormat("dd MMM yyyy");
        cf1 = new WritableCellFormat(df);
        l = new Label(2, 3, "dd MMM yyyy");
        s.addCell(l);
        dt = new DateTime(3, 3, date, cf1);
        s.addCell(dt);
        df = new DateFormat("hh:mm");
        cf1 = new WritableCellFormat(df);
        l = new Label(2, 4, "hh:mm");
        s.addCell(l);
        dt = new DateTime(3, 4, date, cf1);
        s.addCell(dt);
        df = new DateFormat("hh:mm:ss");
        cf1 = new WritableCellFormat(df);
        l = new Label(2, 5, "hh:mm:ss");
        s.addCell(l);
        dt = new DateTime(3, 5, date, cf1);
        s.addCell(dt);
        df = new DateFormat("H:mm:ss a");
        cf1 = new WritableCellFormat(df);
        l = new Label(2, 5, "H:mm:ss a");
        s.addCell(l);
        dt = new DateTime(3, 5, date, cf1);
        s.addCell(dt);
        df = new DateFormat("mm:ss.SSS");
        cf1 = new WritableCellFormat(df);
        l = new Label(2, 6, "mm:ss.SSS");
        s.addCell(l);
        dt = new DateTime(3, 6, date, cf1);
        s.addCell(dt);
        l = new Label(0, 16, "Zero date " + sdf.format(date2), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(0, 17, date2, cf1);
        s.addCell(dt);
        l = new Label(3, 16, "Zero date + 1 " + sdf.format(date5), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(3, 17, date5, cf1);
        s.addCell(dt);
        l = new Label(3, 19, sdf.format(date6), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(3, 20, date6, cf1);
        s.addCell(dt);
        l = new Label(3, 22, sdf.format(date7), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(3, 23, date7, cf1);
        s.addCell(dt);
        l = new Label(3, 25, sdf.format(date8), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(3, 26, date8, cf1);
        s.addCell(dt);
        l = new Label(3, 28, sdf.format(date9), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(3, 29, date9, cf1);
        s.addCell(dt);
        l = new Label(3, 28, sdf.format(date9), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(3, 29, date9, cf1);
        s.addCell(dt);
        l = new Label(3, 31, sdf.format(date10), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(3, 32, date10, cf1);
        s.addCell(dt);
        l = new Label(3, 34, sdf.format(date11), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(3, 35, date11, cf1);
        s.addCell(dt);
        l = new Label(3, 37, sdf.format(date12), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(3, 38, date12, cf1);
        s.addCell(dt);
        l = new Label(0, 19, "Zero UTC date " + sdf.format(date3), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(0, 20, date3, cf1);
        s.addCell(dt);
        l = new Label(0, 22, "Armistice date " + sdf.format(date4), wrappedText);
        s.addCell(l);
        cf1 = new WritableCellFormat(DateFormats.FORMAT9);
        dt = new DateTime(0, 23, date4, cf1);
        s.addCell(dt);
    }

    private void writeLabelFormatSheet(WritableSheet s1) throws WriteException {
        s1.setColumnView(0, 60);
        Label lr = new Label(0, 0, "Arial Fonts");
        s1.addCell(lr);
        lr = new Label(1, 0, "10pt");
        s1.addCell(lr);
        lr = new Label(2, 0, "Normal");
        s1.addCell(lr);
        lr = new Label(3, 0, "12pt");
        s1.addCell(lr);
        WritableFont arial12pt = new WritableFont(WritableFont.ARIAL, 12);
        WritableCellFormat arial12format = new WritableCellFormat(arial12pt);
        arial12format.setWrap(true);
        lr = new Label(4, 0, "Normal", arial12format);
        s1.addCell(lr);
        WritableFont arial10ptBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
        WritableCellFormat arial10BoldFormat = new WritableCellFormat(arial10ptBold);
        lr = new Label(2, 2, "BOLD", arial10BoldFormat);
        s1.addCell(lr);
        WritableFont arial12ptBold = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
        WritableCellFormat arial12BoldFormat = new WritableCellFormat(arial12ptBold);
        lr = new Label(4, 2, "BOLD", arial12BoldFormat);
        s1.addCell(lr);
        WritableFont arial10ptItalic = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
        WritableCellFormat arial10ItalicFormat = new WritableCellFormat(arial10ptItalic);
        lr = new Label(2, 4, "Italic", arial10ItalicFormat);
        s1.addCell(lr);
        WritableFont arial12ptItalic = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, true);
        WritableCellFormat arial12ptItalicFormat = new WritableCellFormat(arial12ptItalic);
        lr = new Label(4, 4, "Italic", arial12ptItalicFormat);
        s1.addCell(lr);
        WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
        WritableCellFormat times10format = new WritableCellFormat(times10pt);
        lr = new Label(0, 7, "Times Fonts", times10format);
        s1.addCell(lr);
        lr = new Label(1, 7, "10pt", times10format);
        s1.addCell(lr);
        lr = new Label(2, 7, "Normal", times10format);
        s1.addCell(lr);
        lr = new Label(3, 7, "12pt", times10format);
        s1.addCell(lr);
        WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
        WritableCellFormat times12format = new WritableCellFormat(times12pt);
        lr = new Label(4, 7, "Normal", times12format);
        s1.addCell(lr);
        WritableFont times10ptBold = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
        WritableCellFormat times10BoldFormat = new WritableCellFormat(times10ptBold);
        lr = new Label(2, 9, "BOLD", times10BoldFormat);
        s1.addCell(lr);
        WritableFont times12ptBold = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
        WritableCellFormat times12BoldFormat = new WritableCellFormat(times12ptBold);
        lr = new Label(4, 9, "BOLD", times12BoldFormat);
        s1.addCell(lr);
        s1.setColumnView(6, 22);
        s1.setColumnView(7, 22);
        s1.setColumnView(8, 22);
        s1.setColumnView(9, 22);
        lr = new Label(0, 11, "Underlining");
        s1.addCell(lr);
        WritableFont arial10ptUnderline = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.SINGLE);
        WritableCellFormat arialUnderline = new WritableCellFormat(arial10ptUnderline);
        lr = new Label(6, 11, "Underline", arialUnderline);
        s1.addCell(lr);
        WritableFont arial10ptDoubleUnderline = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.DOUBLE);
        WritableCellFormat arialDoubleUnderline = new WritableCellFormat(arial10ptDoubleUnderline);
        lr = new Label(7, 11, "Double Underline", arialDoubleUnderline);
        s1.addCell(lr);
        WritableFont arial10ptSingleAcc = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.SINGLE_ACCOUNTING);
        WritableCellFormat arialSingleAcc = new WritableCellFormat(arial10ptSingleAcc);
        lr = new Label(8, 11, "Single Accounting Underline", arialSingleAcc);
        s1.addCell(lr);
        WritableFont arial10ptDoubleAcc = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.DOUBLE_ACCOUNTING);
        WritableCellFormat arialDoubleAcc = new WritableCellFormat(arial10ptDoubleAcc);
        lr = new Label(9, 11, "Double Accounting Underline", arialDoubleAcc);
        s1.addCell(lr);
        WritableFont times14ptBoldUnderline = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD, false, UnderlineStyle.SINGLE);
        WritableCellFormat timesBoldUnderline = new WritableCellFormat(times14ptBoldUnderline);
        lr = new Label(6, 12, "Times 14 Bold Underline", timesBoldUnderline);
        s1.addCell(lr);
        WritableFont arial18ptBoldItalicUnderline = new WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, true, UnderlineStyle.SINGLE);
        WritableCellFormat arialBoldItalicUnderline = new WritableCellFormat(arial18ptBoldItalicUnderline);
        lr = new Label(6, 13, "Arial 18 Bold Italic Underline", arialBoldItalicUnderline);
        s1.addCell(lr);
        lr = new Label(0, 15, "Script styles");
        s1.addCell(lr);
        WritableFont superscript = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK, ScriptStyle.SUPERSCRIPT);
        WritableCellFormat superscriptFormat = new WritableCellFormat(superscript);
        lr = new Label(1, 15, "superscript", superscriptFormat);
        s1.addCell(lr);
        WritableFont subscript = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK, ScriptStyle.SUBSCRIPT);
        WritableCellFormat subscriptFormat = new WritableCellFormat(subscript);
        lr = new Label(2, 15, "subscript", subscriptFormat);
        s1.addCell(lr);
        lr = new Label(0, 17, "Colours");
        s1.addCell(lr);
        WritableFont red = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
        WritableCellFormat redFormat = new WritableCellFormat(red);
        lr = new Label(2, 17, "Red", redFormat);
        s1.addCell(lr);
        WritableFont blue = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
        WritableCellFormat blueFormat = new WritableCellFormat(blue);
        lr = new Label(2, 18, "Blue", blueFormat);
        s1.addCell(lr);
        WritableFont lime = new WritableFont(WritableFont.ARIAL);
        lime.setColour(Colour.LIME);
        WritableCellFormat limeFormat = new WritableCellFormat(lime);
        limeFormat.setWrap(true);
        lr = new Label(4, 18, "Modified palette - was lime, now red", limeFormat);
        s1.addCell(lr);
        WritableCellFormat greyBackground = new WritableCellFormat();
        greyBackground.setWrap(true);
        greyBackground.setBackground(Colour.GRAY_50);
        lr = new Label(2, 19, "Grey background", greyBackground);
        s1.addCell(lr);
        WritableFont yellow = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.YELLOW);
        WritableCellFormat yellowOnBlue = new WritableCellFormat(yellow);
        yellowOnBlue.setWrap(true);
        yellowOnBlue.setBackground(Colour.BLUE);
        lr = new Label(2, 20, "Blue background, yellow foreground", yellowOnBlue);
        s1.addCell(lr);
        lr = new Label(0, 22, "Null label");
        s1.addCell(lr);
        lr = new Label(2, 22, (String)null);
        s1.addCell(lr);
        lr = new Label(0, 24, "A very long label, more than 255 characters\nRejoice O shores\nSing O bells\nBut I with mournful tread\nWalk the deck my captain lies\nFallen cold and dead\nSummer surprised, coming over the Starnbergersee\nWith a shower of rain. We stopped in the Colonnade\nA very long label, more than 255 characters\nRejoice O shores\nSing O bells\nBut I with mournful tread\nWalk the deck my captain lies\nFallen cold and dead\nSummer surprised, coming over the Starnbergersee\nWith a shower of rain. We stopped in the Colonnade\nA very long label, more than 255 characters\nRejoice O shores\nSing O bells\nBut I with mournful tread\nWalk the deck my captain lies\nFallen cold and dead\nSummer surprised, coming over the Starnbergersee\nWith a shower of rain. We stopped in the Colonnade\nA very long label, more than 255 characters\nRejoice O shores\nSing O bells\nBut I with mournful tread\nWalk the deck my captain lies\nFallen cold and dead\nSummer surprised, coming over the Starnbergersee\nWith a shower of rain. We stopped in the Colonnade\nAnd sat and drank coffee an talked for an hour\n", arial12format);
        s1.addCell(lr);
        WritableCellFormat vertical = new WritableCellFormat();
        vertical.setOrientation(Orientation.VERTICAL);
        lr = new Label(0, 26, "Vertical orientation", vertical);
        s1.addCell(lr);
        WritableCellFormat plus_90 = new WritableCellFormat();
        plus_90.setOrientation(Orientation.PLUS_90);
        lr = new Label(1, 26, "Plus 90", plus_90);
        s1.addCell(lr);
        WritableCellFormat minus_90 = new WritableCellFormat();
        minus_90.setOrientation(Orientation.MINUS_90);
        lr = new Label(2, 26, "Minus 90", minus_90);
        s1.addCell(lr);
        lr = new Label(0, 28, "Modified row height");
        s1.addCell(lr);
        s1.setRowView(28, 24);
        lr = new Label(0, 29, "Collapsed row");
        s1.addCell(lr);
        s1.setRowView(29, true);

        Label l;
        try {
            l = new Label(0, 30, "Hyperlink to home page");
            s1.addCell(l);
            URL url = new URL("http://www.andykhan.com/jexcelapi");
            WritableHyperlink wh = new WritableHyperlink(0, 30, 8, 31, url);
            s1.addHyperlink(wh);
            WritableHyperlink wh2 = new WritableHyperlink(7, 30, 9, 31, url);
            s1.addHyperlink(wh2);
            l = new Label(4, 2, "File hyperlink to documentation");
            s1.addCell(l);
            File file = new File("../jexcelapi/docs/index.html");
            wh = new WritableHyperlink(0, 32, 8, 32, file);
            s1.addHyperlink(wh);
            wh = new WritableHyperlink(0, 34, 8, 34, "Link to another cell", s1, 0, 180, 1, 181);
            s1.addHyperlink(wh);
        } catch (MalformedURLException var60) {
            System.err.println(var60.toString());
        }

        l = new Label(5, 35, "Merged cells", timesBoldUnderline);
        s1.mergeCells(5, 35, 8, 37);
        s1.addCell(l);
        l = new Label(5, 38, "More merged cells");
        s1.addCell(l);
        Range r = s1.mergeCells(5, 38, 8, 41);
        s1.insertRow(40);
        s1.removeRow(39);
        s1.unmergeCells(r);
        WritableCellFormat wcf = new WritableCellFormat();
        wcf.setAlignment(jxl.format.Alignment.CENTRE);
        l = new Label(5, 42, "Centred across merged cells", wcf);
        s1.addCell(l);
        s1.mergeCells(5, 42, 10, 42);
        wcf = new WritableCellFormat();
        wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        wcf.setBackground(Colour.GRAY_25);
        l = new Label(3, 44, "Merged with border", wcf);
        s1.addCell(l);
        s1.mergeCells(3, 44, 4, 46);
        WritableFont courier10ptFont = new WritableFont(WritableFont.COURIER, 10);
        WritableCellFormat courier10pt = new WritableCellFormat(courier10ptFont);
        l = new Label(0, 49, "Courier fonts", courier10pt);
        s1.addCell(l);
        WritableFont tahoma12ptFont = new WritableFont(WritableFont.TAHOMA, 12);
        WritableCellFormat tahoma12pt = new WritableCellFormat(tahoma12ptFont);
        l = new Label(0, 50, "Tahoma fonts", tahoma12pt);
        s1.addCell(l);
        WritableFont.FontName wingdingsFont = WritableFont.createFont("Wingdings 2");
        WritableFont wingdings210ptFont = new WritableFont(wingdingsFont, 10);
        WritableCellFormat wingdings210pt = new WritableCellFormat(wingdings210ptFont);
        l = new Label(0, 51, "Bespoke Windgdings 2", wingdings210pt);
        s1.addCell(l);
        WritableCellFormat shrinkToFit = new WritableCellFormat(times12pt);
        shrinkToFit.setShrinkToFit(true);
        l = new Label(3, 53, "Shrunk to fit", shrinkToFit);
        s1.addCell(l);
        l = new Label(3, 55, "Some long wrapped text in a mergec cell", arial12format);
        s1.addCell(l);
        s1.mergeCells(3, 55, 4, 55);
    }

    private void writeBordersSheet(WritableSheet s) throws WriteException {
        s.getSettings().setProtected(true);
        s.setColumnView(1, 15);
        s.setColumnView(2, 15);
        s.setColumnView(4, 15);
        WritableCellFormat thickLeft = new WritableCellFormat();
        thickLeft.setBorder(jxl.format.Border.LEFT, jxl.format.BorderLineStyle.THICK);
        Label lr = new Label(1, 0, "Thick left", thickLeft);
        s.addCell(lr);
        WritableCellFormat dashedRight = new WritableCellFormat();
        dashedRight.setBorder(jxl.format.Border.RIGHT, jxl.format.BorderLineStyle.DASHED);
        lr = new Label(2, 0, "Dashed right", dashedRight);
        s.addCell(lr);
        WritableCellFormat doubleTop = new WritableCellFormat();
        doubleTop.setBorder(jxl.format.Border.TOP, jxl.format.BorderLineStyle.DOUBLE);
        lr = new Label(1, 2, "Double top", doubleTop);
        s.addCell(lr);
        WritableCellFormat hairBottom = new WritableCellFormat();
        hairBottom.setBorder(jxl.format.Border.BOTTOM, jxl.format.BorderLineStyle.HAIR);
        lr = new Label(2, 2, "Hair bottom", hairBottom);
        s.addCell(lr);
        WritableCellFormat allThin = new WritableCellFormat();
        allThin.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        lr = new Label(4, 2, "All thin", allThin);
        s.addCell(lr);
        WritableCellFormat twoBorders = new WritableCellFormat();
        twoBorders.setBorder(jxl.format.Border.TOP, jxl.format.BorderLineStyle.THICK);
        twoBorders.setBorder(jxl.format.Border.LEFT, jxl.format.BorderLineStyle.THICK);
        lr = new Label(6, 2, "Two borders", twoBorders);
        s.addCell(lr);
        lr = new Label(20, 20, "Dislocated cell - after a page break");
        s.addCell(lr);
        s.getSettings().setPaperSize(PaperSize.A3);
        s.getSettings().setOrientation(PageOrientation.LANDSCAPE);
        s.getSettings().setHeaderMargin(2.0);
        s.getSettings().setFooterMargin(2.0);
        s.getSettings().setTopMargin(3.0);
        s.getSettings().setBottomMargin(3.0);
        s.setHeader((String)null, "Page Header", (String)null);
        s.setFooter((String)null, (String)null, "page &P");
        s.addRowPageBreak(18);
        s.insertRow(17);
        s.insertRow(17);
        s.removeRow(17);
        s.addRowPageBreak(30);
        lr = new Label(10, 1, "Hidden column");
        s.addCell(lr);
        lr = new Label(3, 8, "Hidden row");
        s.addCell(lr);
        s.setRowView(8, true);
        WritableCellFormat allThickRed = new WritableCellFormat();
        allThickRed.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THICK, Colour.RED);
        lr = new Label(1, 5, "All thick red", allThickRed);
        s.addCell(lr);
        WritableCellFormat topBottomBlue = new WritableCellFormat();
        topBottomBlue.setBorder(jxl.format.Border.TOP, jxl.format.BorderLineStyle.THIN, Colour.BLUE);
        topBottomBlue.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLUE);
        lr = new Label(4, 5, "Top and bottom blue", topBottomBlue);
        s.addCell(lr);
    }

    private void writeLabelsSheet(WritableSheet ws) throws WriteException {
        ws.getSettings().setProtected(true);
        ws.getSettings().setPassword("jxl");
        ws.getSettings().setVerticalFreeze(5);
        WritableFont wf = new WritableFont(WritableFont.ARIAL, 12);
        wf.setItalic(true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        CellView cv = new CellView();
        cv.setSize(6400);
        cv.setFormat(wcf);
        ws.setColumnView(0, cv);
        ws.setColumnView(1, 15);

        Label l4;
        Label l5;
        for(int i = 0; i < 61; ++i) {
            l4 = new Label(0, i, "Common Label");
            l5 = new Label(1, i, "Distinct label number " + i);
            ws.addCell(l4);
            ws.addCell(l5);
        }

        Label l3 = new Label(0, 61, "Common Label", wcf);
        l4 = new Label(1, 61, "1-1234567890", wcf);
        l5 = new Label(2, 61, "2-1234567890", wcf);
        ws.addCell(l3);
        ws.addCell(l4);
        ws.addCell(l5);

        for(int i = 62; i < 200; ++i) {
            Label l1 = new Label(0, i, "Common Label");
            Label l2 = new Label(1, i, "Distinct label number " + i);
            ws.addCell(l1);
            ws.addCell(l2);
        }

        wf = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
        wf.setColour(Colour.RED);
        wcf = new WritableCellFormat(wf);
        wcf.setWrap(true);
        Label l = new Label(0, 205, "Different format", wcf);
        ws.addCell(l);
    }

    private void writeFormulaSheet(WritableSheet ws) throws WriteException {
        jxl.write.Number nc = new jxl.write.Number(0, 0, 15.0);
        ws.addCell(nc);
        nc = new jxl.write.Number(0, 1, 16.0);
        ws.addCell(nc);
        nc = new jxl.write.Number(0, 2, 10.0);
        ws.addCell(nc);
        nc = new Number(0, 3, 12.0);
        ws.addCell(nc);
        ws.setColumnView(2, 20);
        WritableCellFormat wcf = new WritableCellFormat();
        wcf.setAlignment(Alignment.RIGHT);
        wcf.setWrap(true);
        CellView cv = new CellView();
        cv.setSize(6400);
        cv.setFormat(wcf);
        ws.setColumnView(3, cv);
        Formula f = null;
        Label l = null;
        f = new Formula(2, 0, "A1+A2");
        ws.addCell(f);
        l = new Label(3, 0, "a1+a2");
        ws.addCell(l);
        f = new Formula(2, 1, "A2 * 3");
        ws.addCell(f);
        l = new Label(3, 1, "A2 * 3");
        ws.addCell(l);
        f = new Formula(2, 2, "A2+A1/2.5");
        ws.addCell(f);
        l = new Label(3, 2, "A2+A1/2.5");
        ws.addCell(l);
        f = new Formula(2, 3, "3+(a1+a2)/2.5");
        ws.addCell(f);
        l = new Label(3, 3, "3+(a1+a2)/2.5");
        ws.addCell(l);
        f = new Formula(2, 4, "(a1+a2)/2.5");
        ws.addCell(f);
        l = new Label(3, 4, "(a1+a2)/2.5");
        ws.addCell(l);
        f = new Formula(2, 5, "15+((a1+a2)/2.5)*17");
        ws.addCell(f);
        l = new Label(3, 5, "15+((a1+a2)/2.5)*17");
        ws.addCell(l);
        f = new Formula(2, 6, "SUM(a1:a4)");
        ws.addCell(f);
        l = new Label(3, 6, "SUM(a1:a4)");
        ws.addCell(l);
        f = new Formula(2, 7, "SUM(a1:a4)/4");
        ws.addCell(f);
        l = new Label(3, 7, "SUM(a1:a4)/4");
        ws.addCell(l);
        f = new Formula(2, 8, "AVERAGE(A1:A4)");
        ws.addCell(f);
        l = new Label(3, 8, "AVERAGE(a1:a4)");
        ws.addCell(l);
        f = new Formula(2, 9, "MIN(5,4,1,2,3)");
        ws.addCell(f);
        l = new Label(3, 9, "MIN(5,4,1,2,3)");
        ws.addCell(l);
        f = new Formula(2, 10, "ROUND(3.14159265, 3)");
        ws.addCell(f);
        l = new Label(3, 10, "ROUND(3.14159265, 3)");
        ws.addCell(l);
        f = new Formula(2, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
        ws.addCell(f);
        l = new Label(3, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
        ws.addCell(l);
        f = new Formula(2, 12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
        ws.addCell(f);
        l = new Label(3, 12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
        ws.addCell(l);
        f = new Formula(2, 13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
        ws.addCell(f);
        l = new Label(3, 13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
        ws.addCell(l);
        f = new Formula(2, 14, "IF(A3<=10, \"<= 10\")");
        ws.addCell(f);
        l = new Label(3, 14, "IF(A3<=10, \"<= 10\")");
        ws.addCell(l);
        f = new Formula(2, 15, "SUM(1,2,3,4,5)");
        ws.addCell(f);
        l = new Label(3, 15, "SUM(1,2,3,4,5)");
        ws.addCell(l);
        f = new Formula(2, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
        ws.addCell(f);
        l = new Label(3, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
        ws.addCell(l);
        f = new Formula(2, 17, "3*4+5");
        ws.addCell(f);
        l = new Label(3, 17, "3*4+5");
        ws.addCell(l);
        f = new Formula(2, 18, "\"Plain text formula\"");
        ws.addCell(f);
        l = new Label(3, 18, "Plain text formula");
        ws.addCell(l);
        f = new Formula(2, 19, "SUM(a1,a2,-a3,a4)");
        ws.addCell(f);
        l = new Label(3, 19, "SUM(a1,a2,-a3,a4)");
        ws.addCell(l);
        f = new Formula(2, 20, "2*-(a1+a2)");
        ws.addCell(f);
        l = new Label(3, 20, "2*-(a1+a2)");
        ws.addCell(l);
        f = new Formula(2, 21, "Number Formats!B1/2");
        ws.addCell(f);
        l = new Label(3, 21, "Number Formats!B1/2");
        ws.addCell(l);
        f = new Formula(2, 22, "IF(F22=0, 0, F21/F22)");
        ws.addCell(f);
        l = new Label(3, 22, "IF(F22=0, 0, F21/F22)");
        ws.addCell(l);
        f = new Formula(2, 22, "RAND()");
        ws.addCell(f);
        l = new Label(3, 22, "RAND()");
        ws.addCell(l);
        StringBuffer buf = new StringBuffer();
        buf.append("'");
        buf.append(this.workbook.getSheet(0).getName());
        buf.append("'!");
        buf.append(CellReferenceHelper.getCellReference(9, 18));
        buf.append("*25");
        f = new Formula(2, 23, buf.toString());
        ws.addCell(f);
        l = new Label(3, 23, buf.toString());
        ws.addCell(l);
        wcf = new WritableCellFormat(DateFormats.DEFAULT);
        f = new Formula(2, 24, "NOW()", wcf);
        ws.addCell(f);
        l = new Label(3, 24, "NOW()");
        ws.addCell(l);
        f = new Formula(2, 25, "$A$2+A3");
        ws.addCell(f);
        l = new Label(3, 25, "$A$2+A3");
        ws.addCell(l);
        f = new Formula(2, 26, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
        ws.addCell(f);
        l = new Label(3, 26, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
        ws.addCell(l);
        f = new Formula(2, 27, "SUM(A1,A2,A3,A4)");
        ws.addCell(f);
        l = new Label(3, 27, "SUM(A1,A2,A3,A4)");
        ws.addCell(l);
        l = new Label(1, 28, "a1");
        ws.addCell(l);
        f = new Formula(2, 28, "SUM(INDIRECT(ADDRESS(2,29)):A4)");
        ws.addCell(f);
        l = new Label(3, 28, "SUM(INDIRECT(ADDRESS(2,29):A4)");
        ws.addCell(l);
        f = new Formula(2, 29, "COUNTIF(A1:A4, \">=12\")");
        ws.addCell(f);
        l = new Label(3, 29, "COUNTIF(A1:A4, \">=12\")");
        ws.addCell(l);
    }

    private void writeImageSheet(WritableSheet ws) throws WriteException {
        Label l = new Label(0, 0, "Weald & Downland Open Air Museum, Sussex");
        ws.addCell(l);
        WritableImage wi = new WritableImage(0.0, 3.0, 5.0, 7.0, new File("resources/wealdanddownland.png"));
        ws.addImage(wi);
        l = new Label(0, 12, "Merchant Adventurers Hall, York");
        ws.addCell(l);
        wi = new WritableImage(5.0, 12.0, 4.0, 10.0, new File("resources/merchantadventurers.png"));
        ws.addImage(wi);
    }
}
